package ctn.dao.hibernate.impl;

import java.math.BigInteger;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Component;

import ctn.convert.CPAInfoCovert;
import ctn.convert.CPAVerCovert;
import ctn.dao.inteface.CPAInfoDao;
import ctn.hibernate.po.CpaFun;
import ctn.hibernate.po.CtnCpaInfo;
import ctn.hibernate.po.CtnCpaVer;
import ctn.hibernate.po.CtnGrp;
import ctn.hibernate.po.CtnUser;
import ctn.hibernate.po.CtnUserMsgParm;
import ctn.service.dto.MyProjectDTO;
import ctn.service.dto.PlugDTO;
import ctn.util.jmesa.DTOFilter;
import ctn.util.jmesa.DTOSorter;
import ctn.util.jmesa.PaginationCriteria;
@Component
public class CPAInfoDaoImpl extends AbstractCTNDAO implements CPAInfoDao {
	//private static DateFormat   df=new   SimpleDateFormat( "yyyy-MM-dd   hh:mm"); 
	
	@SuppressWarnings("unchecked")
	@Override
	public Long getplugListCountByCriteria(final PaginationCriteria criteria) {
		
		return (Long) getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<DTOFilter> filters=criteria.getPageFilter().getFilters();
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT count(*) FROM CtnCpaInfo where 1=1 ");
				getFiler(filters, sb);
				
				Query query=session.createQuery(sb.toString());
				setParmeter(filters, query);
				return query.uniqueResult();
			}
			
		});
	}
	
	
	@Override
	public void delPlug(Long id) {
		getHibernateTemplate().delete(queryPlug(id));
	}


	@Override
	public CtnCpaInfo queryPlug(Long id){
		
		return   getHibernateTemplate().get(CtnCpaInfo.class, id);
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<PlugDTO> getWplugListByCriteria(final PaginationCriteria criteria) {
		return (List<PlugDTO>) getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<DTOFilter> filters=criteria.getPageFilter().getFilters();
				StringBuffer sb=new StringBuffer();
				sb.append("FROM CtnCpaInfo where 1=1 ");
				getFiler(filters, sb);
				getSorter(criteria.getPageSorter().getSorts(), sb);
				Query query=session.createQuery(sb.toString());
				setParmeter(filters, query);
				query.setFirstResult(criteria.getRowStart());
				query.setMaxResults(criteria.getRowEnd()
						- criteria.getRowStart());
				List<CtnCpaInfo> cpas=query.list();
				List<PlugDTO> plugs=new ArrayList<PlugDTO>();
				for(CtnCpaInfo cpa:cpas){
					PlugDTO plug=new PlugDTO();
					plug.setID(cpa.getId());
					plug.setCpaName(cpa.getName());
					plug.setCreateDate(cpa.getCreateDate());
					plug.setCreateUserName(cpa.getCreateUser());
					Set<CtnCpaVer> vers=cpa.getCtnCpaVers();
					CtnCpaVer ver=getLastVer(vers);
					if(ver!=null){
						if(null!=ver.getCompleteDate()){
							plug.setCompleteDate(ver.getCompleteDate());
						}
						if(null!=ver.getEffDate()){
							plug.setEffDate(ver.getEffDate());
						}
						plug.setVerID(ver.getId());
//						plug.setFileName(ver.getFileName());
//						plug.setProdFileName(ver.getProdFileName());
						plug.setVersion(ver.getVersion());
						if(null!=ver.getCtnGrpByImplGrpId()){
							plug.setImplGrpID(ver.getCtnGrpByImplGrpId().getId());
						}
						if(null!=ver.getCtnGrpByWatchGrpId()){
							plug.setWatchGrpID(ver.getCtnGrpByWatchGrpId().getId());
						}
						plug.setVerStatus(ver.getStatus());
					}
					plugs.add(plug);
				}
				return plugs;
			}
			
		});
	}
	
	private CtnCpaVer getLastVer(Set<CtnCpaVer> vers){
		Long ID=0L;
		CtnCpaVer rtnver=null;
		for(CtnCpaVer ver:vers){
			if(ID<ver.getId()){
				ID=ver.getId();
				rtnver=ver;
			}
		}
		return rtnver;
	}
	private void getFiler(List<DTOFilter> filters,StringBuffer sb){
		for(DTOFilter filter:filters){
			if("status".equals(filter.getProperty())){
				sb.append(" AND status=:status ");
			}
		}
	}
	private void getSorter(List<DTOSorter> sorters,StringBuffer sb){
		if (sorters != null && sorters.size() > 0) {
			sb.append("order by ");
		} else {
			sb.append("ORDER BY ID ASC");
			return;
		}
		for (int i = 0; i < sorters.size() && sorters != null; i++) {
			DTOSorter sort = sorters.get(i);
			String sorterField = "";
			if ("ID".equals(sort.getProperty())) {
				sorterField = "ID";
			}
			if ("cpaName".equals(sort.getProperty())) {
				sorterField = "cpaName";
			}
			if ("createUserName".equals(sort.getProperty())) {
				sorterField = "createUserName";
			}
			if ("createDate".equals(sort.getProperty())) {
				sorterField = "createDate";
			}
			if (i != sorters.size() - 1)
				sb.append(sorterField + " " + sort.getOrder() 
						+ ", ");
			else
				sb.append(sorterField + " " + sort.getOrder() );
		}
	}
	private void setParmeter(List<DTOFilter> filters,Query query){
		for(DTOFilter filter:filters){
			if("status".equals(filter.getProperty())){
				query.setParameter("status", filter.getValue());
			}
		}
	}
	@Override
	public void createPlug(CtnCpaInfo info) {
		getHibernateTemplate().save(info);
	}


	@Override
	public void editPlug(CtnCpaInfo info) {
		getHibernateTemplate().update(info);
		
	}
	

	
	public List<CtnCpaInfo> getAllByStatus(String status){
		return (List<CtnCpaInfo>)getHibernateTemplate().find("from CtnCpaInfo i where i.status = ?", new Object[]{status});
	}


	@Override
	public void updateCPAFunStatus(String funID, String status) {
		CpaFun cpaFun=getHibernateTemplate().get(CpaFun.class, Long.parseLong(funID));
		cpaFun.setStatus(status);
		getHibernateTemplate().update(cpaFun);
		
	}


	@Override
	public List<MyProjectDTO> getListMyProject(final PaginationCriteria criteria) {
		return getHibernateTemplate().execute(new HibernateCallback<List<MyProjectDTO>>(){

			@Override
			public List<MyProjectDTO> doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT cpa_fun.FUN_NAME,");
				sb.append("cpa_fun.ID,cpa_fun.START_DATE,cpa_fun.COMPLETE_DATE,cpa_fun.PLAN_END_DATE,cpa_fun.`STATUS`,ctn_cpa_info.NAME  ");
				sb.append("FROM cpa_fun ,ctn_cpa_info ");
				sb.append("WHERE cpa_fun.CPA_ID =  ctn_cpa_info.ID  ");
				getListMyProjectFilter(criteria,sb);
				getListMyProjectSorter(criteria,sb);
				Query query=session.createSQLQuery(sb.toString().substring(0,sb.toString().length()-1));
				try {
					setMyProjectParamter(criteria,query);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				query.setMaxResults(criteria.getRowEnd()-criteria.getRowStart());
				query.setFirstResult(criteria.getRowStart());
				List<Object[]> list=query.list();
				List<MyProjectDTO> dtos=new ArrayList<MyProjectDTO>();
				for(Object[] obj:list){
					MyProjectDTO dto=new MyProjectDTO();
					dto.setFunName((String)obj[0]);
					dto.setFunID(String.valueOf(obj[1]));
					dto.setCreateDate((Date)obj[2]);
					dto.setEndDate((Date)obj[3]);
					dto.setPlanEndDate((Date)obj[4]);
					dto.setFunStatus((String)obj[5]);
					dto.setProName((String)obj[6]);
					dtos.add(dto);
				}
				return dtos;
			}

		});
	}


	@Override
	public Long getListMyProjectCount(final PaginationCriteria criteria) {
		return getHibernateTemplate().execute(new HibernateCallback<Long>(){

			@Override
			public Long doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT count(*) ");
				sb.append("FROM cpa_fun ,ctn_cpa_info ");
				sb.append("WHERE cpa_fun.CPA_ID =  ctn_cpa_info.ID  ");
				getListMyProjectFilter(criteria,sb);
				getListMyProjectSorter(criteria,sb);
				Query query=session.createSQLQuery(sb.toString().substring(0,sb.toString().length()-1));
				try {
					setMyProjectParamter(criteria,query);
				} catch (ParseException e) {
					e.printStackTrace();
				}
				return ((BigInteger) query.uniqueResult()).longValue();
			}

			});
	}
	private void getListMyProjectSorter(PaginationCriteria criteria,StringBuffer sb) {
		List<DTOSorter> soters=criteria.getPageSorter().getSorts();
		sb.append("order by ");
		if(soters.isEmpty()){
			sb.append(" cpa_fun.PLAN_END_DATE DESC,");
		}
		for(DTOSorter sorte:soters){
			if("proName".equals(sorte.getProperty())){
				sb.append(" ctn_cpa_info.NAME ").append(sorte.getOrder()).append(",");
			}else if("funStatus".equals(sorte.getProperty())){
				sb.append(" cpa_fun.STATUS ").append(sorte.getOrder()).append(",");
			}else if("createDate".equals(sorte.getProperty())){
				sb.append(" cpa_fun.START_DATE ").append(sorte.getOrder()).append(",");
			}else if("funName".equals(sorte.getProperty())){
				sb.append(" cpa_fun.FUN_NAME ").append(sorte.getOrder()).append(",");
			}else if("userID".equals(sorte.getProperty())){
				sb.append(" cpa_fun.IMPL_USER_ID ").append(sorte.getOrder()).append(",");
			}else if("endDate".equals(sorte.getProperty())){
				sb.append(" cpa_fun.COMPLETE_DATE ").append(sorte.getOrder()).append(",");
			}else if("planEndDate".equals(sorte.getProperty())){
				sb.append(" cpa_fun.PLAN_END_DATE ").append(sorte.getOrder()).append(",");
			}
		}
	}
	private void getListMyProjectFilter(PaginationCriteria criteria,StringBuffer sb) {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("proName".equals(filter.getProperty())){
				sb.append("AND ctn_cpa_info.NAME like :proName ");
			}else if("funStatus".equals(filter.getProperty())){
				sb.append("AND cpa_fun.STATUS like :funStatus ");
			}else if("createDate".equals(filter.getProperty())){
				sb.append("AND cpa_fun.START_DATE =:createDate ");
			}else if("funName".equals(filter.getProperty())){
				sb.append("AND cpa_fun.FUN_NAME like :funName ");
			}else if("userID".equals(filter.getProperty())){
				sb.append("AND cpa_fun.IMPL_USER_ID =:userID ");
			}else if("endDate".equals(filter.getProperty())){
				sb.append("AND cpa_fun.COMPLETE_DATE =:endDate ");
			}else if("planEndDate".equals(filter.getProperty())){
				sb.append("AND cpa_fun.PLAN_END_DATE =:planEndDate ");
			}
		}
	}
	private void setMyProjectParamter(PaginationCriteria criteria,Query query) throws HibernateException, ParseException {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("proName".equals(filter.getProperty())){
				query.setString("proName", "%"+filter.getValue()+"%");
			}else if("funStatus".equals(filter.getProperty())){
				query.setString("funStatus", "%"+filter.getValue()+"%");
			}else if("createDate".equals(filter.getProperty())){
				query.setParameter("createDate", filter.getValue());
			}else if("funName".equals(filter.getProperty())){
				query.setString("funName", "%"+filter.getValue()+"%");
			}else if("userID".equals(filter.getProperty())){
				query.setParameter("userID", filter.getValue());
			}else if("endDate".equals(filter.getProperty())){
				query.setParameter("endDate", filter.getValue());
			}else if("planEndDate".equals(filter.getProperty())){
				query.setParameter("planEndDate", filter.getValue());
			}
		}		
	}


	@Override
	public List<MyProjectDTO> getListMyCreateProject(final PaginationCriteria criteria) {
		return getHibernateTemplate().execute(new HibernateCallback<List<MyProjectDTO>>(){

			@Override
			public List<MyProjectDTO> doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT info.NAME,");
				sb.append("info.ID,info.CREATE_USER,info.CREATE_DATE,info.`STATUS`,ver.`VERSION` ");
				sb.append("FROM ctn_cpa_info AS info , ctn_cpa_ver AS ver ");
				sb.append("WHERE info.ID=ver.cpa_ID  ");
				getListMyCreateProjectFilter(criteria,sb);
				getListMyCreateProjectSorter(criteria,sb);
				Query query=session.createSQLQuery(sb.toString().substring(0,sb.toString().length()-1));
				try {
					setMyCreateProjectParamter(criteria,query);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				query.setMaxResults(criteria.getRowEnd()-criteria.getRowStart());
				query.setFirstResult(criteria.getRowStart());
				List<Object[]> list=query.list();
				List<MyProjectDTO> dtos=new ArrayList<MyProjectDTO>();
				for(Object[] obj:list){
					MyProjectDTO dto=new MyProjectDTO();
					dto.setProName((String)obj[0]);
					dto.setFunID(String.valueOf(obj[1]));
					dto.setCreateUserID(String.valueOf(obj[2]));
					dto.setCreateDate((Date)obj[3]);
					dto.setFunStatus((String)obj[4]);
					dto.setVer((String)obj[5]);
					dtos.add(dto);
				}
				return dtos;
			}

		});
	}


	@Override
	public Long getListMyCreateProjectCount(final PaginationCriteria criteria) {
		return getHibernateTemplate().execute(new HibernateCallback<Long>(){

			@Override
			public Long doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT count(*) ");
				sb.append("FROM ctn_cpa_info AS info , ctn_cpa_ver AS ver ");
				sb.append("WHERE info.ID=ver.cpa_ID  ");
				getListMyCreateProjectFilter(criteria,sb);
				getListMyCreateProjectSorter(criteria,sb);
				Query query=session.createSQLQuery(sb.toString().substring(0,sb.toString().length()-1));
				try {
					setMyCreateProjectParamter(criteria,query);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return ((BigInteger) query.uniqueResult()).longValue();
			}

			
			});
	}
	private void setMyCreateProjectParamter(PaginationCriteria criteria,Query query) throws HibernateException, ParseException {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("proName".equals(filter.getProperty())){
				query.setString("proName", "%"+filter.getValue()+"%");
			}else if("funStatus".equals(filter.getProperty())){
				query.setString("status", "%"+filter.getValue()+"%");
			}else if("createDate".equals(filter.getProperty())){
				query.setParameter("createDate", filter.getValue());
			}else if("ver".equals(filter.getProperty())){
				query.setParameter("ver", filter.getValue());
			}else if("userID".equals(filter.getProperty())){
				query.setParameter("userID", filter.getValue());
			}
			
		}
	}

	private void getListMyCreateProjectSorter(
			PaginationCriteria criteria,StringBuffer sb) {
		List<DTOSorter> soters=criteria.getPageSorter().getSorts();
		sb.append("order by ");
		if(soters.isEmpty()){
			sb.append(" info.CREATE_DATE DESC,");
		}
		for(DTOSorter sorte:soters){
			if("proName".equals(sorte.getProperty())){
				sb.append(" info.NAME ").append(sorte.getOrder()).append(",");
			}else if("funStatus".equals(sorte.getProperty())){
				sb.append(" info.STATUS ").append(sorte.getOrder()).append(",");
			}else if("createDate".equals(sorte.getProperty())){
				sb.append(" info.CREATE_DATE ").append(sorte.getOrder()).append(",");
			}else if("ver".equals(sorte.getProperty())){
				sb.append(" ver.VERSION  ").append(sorte.getOrder()).append(",");
			}else if("userID".equals(sorte.getProperty())){
				sb.append(" info.create_user ").append(sorte.getOrder()).append(",");
			}
		}
	}

	private void getListMyCreateProjectFilter(
			PaginationCriteria criteria,StringBuffer sb) {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("proName".equals(filter.getProperty())){
				sb.append("AND info.NAME like :proName ");
			}else if("funStatus".equals(filter.getProperty())){
				sb.append("AND info.STATUS like :status ");
			}else if("createDate".equals(filter.getProperty())){
				sb.append("AND info.CREATE_DATE =:createDate ");
			}else if("ver".equals(filter.getProperty())){
				sb.append("AND ver.VERSION =:ver ");
			}else if("userID".equals(filter.getProperty())){
				sb.append("AND info.create_user =:userID ");
			}
		}
	}


	@Override
	public Long getCreateProjUserIDbyFunID(String funID,Set<CtnUserMsgParm> parms) {
		CpaFun fun=getHibernateTemplate().load(CpaFun.class, Long.parseLong(funID));
		String userName=fun.getCtnCpaInfo().getCreateUser();
		parms.add(new CtnUserMsgParm("TL",fun.getCtnCpaInfo().getName()));
		parms.add(new CtnUserMsgParm("MN",fun.getFunName()));
		CtnUser userTmp=new CtnUser();
		userTmp.setName(userName);
		CtnUser user=(CtnUser) getHibernateTemplate().findByExample(userTmp);
		return userTmp.getId();
	}


	@Override
	public void updateCPAFun(CpaFun fun) {
		getHibernateTemplate().saveOrUpdate(fun);
	}


	@Override
	public CpaFun getCpaFunByID(String funID) {
		return getHibernateTemplate().get(CpaFun.class, Long.parseLong(funID));
	}

	
	@Override
	public void updateCpaVerStatus(String ID, String status) {
		CtnCpaVer ctnCpaVer=getHibernateTemplate().get(CtnCpaVer.class, Long.parseLong(ID));
		ctnCpaVer.setStatus(status);
		getHibernateTemplate().update(ctnCpaVer);
	}
	@Override
	public void updateCpaVer(CtnCpaVer ver) {
		
		getHibernateTemplate().saveOrUpdate(ver);
	}

	@Override
	public CtnCpaInfo getCpaInfo(Long id) {
		return getHibernateTemplate().get(CtnCpaInfo.class, id);
	}


	@Override
	public List<CtnGrp> getAllGrps() {
		
		return getHibernateTemplate().find("From CtnGrp");
	}


	@Override
	public PlugDTO getWplug(Long id) {
		PlugDTO plugDTO=new PlugDTO();
		CtnCpaInfo cpa=this.queryPlug(id);
		CPAInfoCovert.convertPo2DTO(cpa,plugDTO);
		
		Set<CtnCpaVer> vers=cpa.getCtnCpaVers();
		CtnCpaVer ver=getLastVer(vers);
		if(null!=ver){
			CPAVerCovert.convertVER2Po(ver,plugDTO);
		}
		return plugDTO;
	}
}
